package com.ag.wzp.service.impl;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.apache.log4j.Logger;
import org.springframework.stereotype.Service;

import com.ag.bean.TbPropose;
import com.ag.common.IDUtil;
import com.ag.common.PageUtil;
import com.ag.wzp.dao.WzpTbEventDAO;
import com.ag.wzp.dao.WzpTbProposeDAO;
/**
 * 事件查询
 * @author 温镇澎
 *
 */
@Service("queryService")
public class QueryService {
	private static final Logger logger = Logger.getLogger(QueryService.class);
	@Resource(name="WzpTbEventDAO")
	private WzpTbEventDAO eventDAO;
/**
 * Ljh 	
 */
	@Resource(name="WzpTbProposeDAO")
	private WzpTbProposeDAO proposeDAO;
	
	public List<String> getEidByUid(String uId){
		List<TbPropose> tbProposes = proposeDAO.findByA(uId);
		List<String> list = new ArrayList<String>();
		for(TbPropose tbPropose : tbProposes){
			list.add(tbPropose.getTbEvent().getEId());
		}
		return list;
	}
	
	public PageUtil query(Map<String, String> queryOption) {
		StringBuilder countsql = new StringBuilder();
		StringBuilder hsql = new StringBuilder();
		countsql.append("select count(*) from TbEvent e left join e.tbEventStatus left join e.tbEventLevel left join e.tbEventType ");
		hsql.append("from TbEvent e left join fetch e.tbEventLevel left join fetch e.tbEventStatus left join fetch e.tbEventType ");
//		if(queryOption.containsKey("UId")&& !queryOption.get("UId").isEmpty()){
//			countsql.append(" left join e.tbEventmackups mack ");
//			hsql.append(" left join fetch e.tbEventmackups mack ");
//		}
		countsql.append(" where e.a = "+IDUtil.PRESERVE);
		hsql.append(" where e.a = "+IDUtil.PRESERVE);
		List result = null;
		int firstRow = 0 ,maxRow = 10,page = 1,total = 0;
		if (!(queryOption == null)) {
			// 获取总数
			try {
				total = ((Long) this.eventDAO.find(
						spliceHQL(countsql, queryOption).toString()).iterator()
						.next()).intValue();
			} catch (Exception e) {
				e.printStackTrace();
				return null;
			}
			if (queryOption.containsKey("page")
					&& !queryOption.get("page").isEmpty()) {
				page = Integer.parseInt(queryOption.get("page"));
				logger.info("page=" + page);
				firstRow = maxRow * (page - 1);
			}
			if (total != 0) {
				try {
					result = this.eventDAO.query(appendOrder(spliceHQL(hsql, queryOption),queryOption)
							.toString(), firstRow, maxRow);
				} catch (Exception e) {
					e.printStackTrace();
					return null;
				}
				logger.info("result=" + result.size());
			}

		} else {
			// 第一次查询,无参数查询
			logger.info("queryOption=" + queryOption + "---");
			try {
				total = ((Long) this.eventDAO.find(countsql.toString())
						.iterator().next()).intValue();
			} catch (Exception e) {
				e.printStackTrace();
				return null;
			}
			if (total != 0) {
				try {
					result = this.eventDAO.query(hsql.toString()+" order by e.EId desc ", firstRow, maxRow);
				} catch (Exception e) {
					e.printStackTrace();
					return null;
				}
				logger.info("result=" + result.size());
			}

		}
		logger.info("total="+total);
		PageUtil pageUitl = new PageUtil();
		pageUitl.setMaxRow(maxRow);
		pageUitl.setPage(page);
		pageUitl.setTotal(total);
		pageUitl.setList(result);
		return pageUitl;
	}
	
	private StringBuilder spliceHQL(StringBuilder sql,
			Map<String, String> queryOption) {
		if (queryOption.containsKey("elId")
				&&  !"0".equals(queryOption.get("elId"))) {
			sql.append(" and e.tbEventLevel.elId = '");
			sql.append(queryOption.get("elId")+"'");
		}
		//查询条件是多个事件等级
		if (queryOption.containsKey("elIds")
				&&  !queryOption.get("elIds").isEmpty()) {
			sql.append(" and e.tbEventLevel.elId in (");
			String[] elIds = queryOption.get("elIds").split(",");
			logger.info("elIds"+elIds.toString());
			for(String id : elIds){
				if (id.length() > 0) {
				logger.info("id="+id);
				sql.append("'"+id+"',");
				}
			}
			sql.deleteCharAt(sql.length()-1);
			sql.append(")");
		}
		if (queryOption.containsKey("tbesId")
				&& !"0".equals(queryOption.get("tbesId"))) {
			sql.append(" and e.tbEventStatus.tbesId = '");
			sql.append(queryOption.get("tbesId")+"'");
/*			logger.info("tbesIds");
			sql.append(" and e.tbEventStatus.tbesId in (");
			String[] tbesIds = queryOption.get("tbesIds").split(",");
			logger.info("tb"+tbesIds.toString());
			for(String id : tbesIds){
				if (id.length() > 0) {
				logger.info("id="+id);
				sql.append("'"+id+"',");
				}
			}
			sql.deleteCharAt(sql.length()-1);
			sql.append(")");
			*/
		}
		if (queryOption.containsKey("etId")
				&&  !"0".equals(queryOption.get("etId"))) {
			sql.append(" and e.tbEventType.etId = '");
			sql.append(queryOption.get("etId")+"'");
		}
		if (queryOption.containsKey("esId")
				&&  !"0".equals(queryOption.get("esId"))) {
			sql.append(" and e.tbEventSource.esId = '");
			sql.append(queryOption.get("esId")+"'");
		}
		if (queryOption.containsKey("EId")
				&& !queryOption.get("EId").isEmpty()) {
			sql.append(" and e.EId = '");
			sql.append(queryOption.get("EId")+"'");
		}
		if (queryOption.containsKey("eventTimeStart")
				&& !queryOption.get("eventTimeStart").isEmpty()) {
			sql.append(" and e.EEventtime >= '"+queryOption.get("eventTimeStart")+ "'");
			//sql.append("to_date('"+queryOption.get("eventTimeStart")+"','YYYY-MM-DD') ");
		}
		if (queryOption.containsKey("eventTimeEnd")
				&& !queryOption.get("eventTimeEnd").isEmpty()) {
			sql.append(" and e.EEventtime <= '"+queryOption.get("eventTimeEnd")+ "'");
			//sql.append("to_date('"+queryOption.get("eventTimeStart")+"','YYYY-MM-DD') ");
		}
		if(queryOption.containsKey("UId")&& !queryOption.get("UId").isEmpty()){
			String UId = queryOption.get("UId");
			//与事件和人有关的表：事件（TbEvent),事件补充（TbEventmackup），处置建议（TbDisposalAdvice），
			//呈批（TbPropose），呈批记录表（TbProposeRecord）,任务（TbTask），事件报告（TbEventReport）
			//sql.append(" and e.EId in( select mack.tbEvent.EId from TbEventmackup mack where  mack.tbUser.UId = '"+UId+"')");
//			sql.append(" and e.EId in( select distinct e.EId from TbEvent e where e.tbUserByUId.UId = '"+UId+"' or e.tbUserByTbUId.UId = '"+UId+"' or e.ENotifyLeader = '"+UId+"'"+
//					" or e.EId in ( select distinct mack.tbEvent.EId from TbEvent e, TbEventmackup mack where mack.tbUser.UId = '"+UId+"')" +
//					" or e.EId in ( select distinct contlog.c from TbContactLog contlog where contlog.a = '"+UId+"')" +
//					" or e.EId in ( select distinct da.tbEvent.EId from TbEvent e, TbDisposalAdvice da where da.tbUserByApplyUId.UId = '"+UId+"'" +
//							" or da.tbUserByAdviceUId.UId = '"+UId+"' or da.tbUserBySubmitUId.UId = '"+UId+"')" +
//					" or e.EId in ( select distinct pro.tbEvent.EId from TbEvent e, TbPropose pro left join pro.tbProposeRecords proRec  " +
//						//" left join proRec.tbUserByPrCheckId prchu left join proRec.tbUserByPrId pru " +
//						" where pro.tbUserByPViceCommander.UId = '"+UId+"' or pro.tbUserByPTeamLeader.UId = '"+UId+"' or pro.tbUserByPCommander.UId = '"+UId+"'" +
//						//" or prchu.UId = '"+ UId+"' or pru.UId = '" +UId+"')"+
//						" or proRec.tbUserByPrCheckId.UId = '"+ UId+"' or proRec.tbUserByPrId.UId = '" +UId+"')"+
//					" )");
			sql.append(" and e.EId in( select distinct e.EId from TbEvent e where e.tbUserByUId.UId = '"+UId+"' or e.tbUserByTbUId.UId = '"+UId+"' or e.ENotifyLeader = '"+UId+"'"+
					" or e.EId in ( select distinct mack.tbEvent.EId from TbEvent e, TbEventmackup mack where mack.tbUser.UId = '"+UId+"')" +
					" or e.EId in ( select distinct contlog.c from TbContactLog contlog where contlog.a = '"+UId+"')" +
					" or e.EId in ( select distinct da.tbEvent.EId from TbEvent e, TbDisposalAdvice da where da.tbUserByApplyUId.UId = '"+UId+"'" +
							" or da.tbUserByAdviceUId.UId = '"+UId+"' or da.tbUserBySubmitUId.UId = '"+UId+"')" +
					" or e.EId in ( select distinct pro.tbEvent.EId from TbEvent e, TbPropose pro left join pro.tbProposeRecords proRec  " +
						//" left join proRec.tbUserByPrCheckId prchu left join proRec.tbUserByPrId pru " +
						" where pro.tbUserByPViceCommander.UId = '"+UId+"' or pro.tbUserByPTeamLeader.UId = '"+UId+"' or pro.tbUserByPCommander.UId = '"+UId+"' or pro.a = '"+UId+"'" +
						//" or prchu.UId = '"+ UId+"' or pru.UId = '" +UId+"')"+
						" or proRec.tbUserByPrCheckId.UId = '"+ UId+"' or proRec.tbUserByPrId.UId = '" +UId+"')"+
					" )");
//			sql.append(" and e.EId in( select distinct e.EId from TbEvent e where e.tbUserByUId.UId = '"+UId+"' or e.tbUserByTbUId.UId = '"+UId+"' or e.ENotifyLeader = '"+UId+"'"+
//					" or e.EId in ( select distinct mack.tbEvent.EId from TbEvent e, TbEventmackup mack where mack.tbUser.UId = '"+UId+"')" +
//					
//					" or e.EId in ( select distinct da.tbEvent.EId from TbEvent e, TbDisposalAdvice da where da.tbUserByApplyUId.UId = '"+UId+"'" +
//					" or da.tbUserByAdviceUId.UId = '"+UId+"' or da.tbUserBySubmitUId.UId = '"+UId+"')" +
//			" )");
		}
		logger.info("sql = "+ sql);
		return sql;
	}
	
	private StringBuilder appendOrder(StringBuilder sql,
			Map<String, String> queryOption){
		if (queryOption.containsKey("order")) {
			logger.info("order"+queryOption.get("order"));
			switch (new Integer(queryOption.get("order"))) {
			case 0:
				sql.append(" order by e.EId desc ");
				break;
			case 1:
				sql.append(" order by e.tbEventLevel.elId desc, e.EId desc ");
				break;
			case 2:
				sql.append(" order by e.tbEventLevel.elId asc, e.EId desc ");
				break;
			case 3:
				sql.append(" order by e.EEventtime asc, e.EId desc ,e.tbEventLevel.elId asc ");
				break;
			case 4:
				sql.append(" order by e.EEventtime desc, e.EId desc,e.tbEventLevel.elId asc ");
				break;
			default:
				sql.append(" order by e.EId desc ");
				break;
			}
		}
		return sql;
	}
}
